﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

/// <summary>
/// Inventory 的摘要说明
/// </summary>
public sealed class Inventory
{
    static readonly string CONNECTION_STRING = ConfigurationManager.ConnectionStrings["SMS_Online"].ConnectionString;

    public static List<Order> getOrdersByCondition(int RegionID, string StoreID, DateTime? beginOrderDate, DateTime? endOrderDate, string status)
    {
        List<Order> list = new List<Order>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetOrdersByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginOrderDate", beginOrderDate.HasValue ? beginOrderDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endOrderDate", endOrderDate.HasValue ? endOrderDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@status", string.IsNullOrEmpty(status) ? System.DBNull.Value.ToString() : status);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    bool? isAccept = null;
                    DateTime? auditingDate = null;
                    list.Add(new Order(
                        reader["OrderID"].ToString(),
                        reader["OrderName"].ToString(),
                        bool.Parse(reader["IsRush"].ToString()),
                        DateTime.Parse(reader["OrderDate"].ToString()),
                        DateTime.Parse(reader["ArriveDate"].ToString()),
                        reader["IsAccept"].ToString() == System.DBNull.Value.ToString() ? isAccept : bool.Parse(reader["IsAccept"].ToString()),
                        reader["AuditingDate"].ToString() == System.DBNull.Value.ToString() ? auditingDate : DateTime.Parse(reader["AuditingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["AuditingDescription"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString()

                        ));
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<OrderDetail> getOrderDetailByOrderID(string OrderID)
    {
        List<OrderDetail> list = new List<OrderDetail>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandText = string.Format("exec usp_GetOrderDetailByOrderID '{0}'", OrderID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new OrderDetail(OrderID,
                        reader["MaterialID"].ToString(),
                        reader["MaterialName"].ToString(),
                        reader["OrderUnit"].ToString(),
                        reader["Spec"].ToString(),
                        float.Parse(reader["qty"].ToString())));
                }
                reader.Close();
            }
        }
        return list;
    }
    public static Order getOrder(string OrderID)
    {
        Order order = null;
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandText = string.Format("usp_GetOrder '{0}'", OrderID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    bool? isAccept = null;
                    DateTime? auditingDate = null;
                    order = new Order(
                        reader["OrderID"].ToString(),
                        reader["OrderName"].ToString(),
                        bool.Parse(reader["IsRush"].ToString()),
                        DateTime.Parse(reader["OrderDate"].ToString()),
                        DateTime.Parse(reader["ArriveDate"].ToString()),
                        reader["IsAccept"].ToString() == System.DBNull.Value.ToString() ? isAccept : bool.Parse(reader["IsAccept"].ToString()),
                        reader["AuditingDate"].ToString() == System.DBNull.Value.ToString() ? auditingDate : DateTime.Parse(reader["AuditingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["AuditingDescription"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString()

                        );
                }
                reader.Close();
            }
        }
        return order;
    }
    public static bool AuditingOrder(string StoreID,string OrderID, bool isAccept, string AuditingDescription)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_AuditingOrder";
                com.Parameters.AddWithValue("@StoreID", StoreID);
                com.Parameters.AddWithValue("@OrderID", OrderID);
                com.Parameters.AddWithValue("@IsAccept", isAccept);
                com.Parameters.AddWithValue("@AuditingDescription", AuditingDescription);
                com.Parameters.AddWithValue("@msg_id", DateTime.Now.ToString("yyyyMMdd_HHmmss_ff"));
                com.Connection = con;
                con.Open();
                int r = com.ExecuteNonQuery();
                if (r > 0)
                {
                    return true;
                }
            }
        }
        return false;
    }


    public static List<Receive> getReceivesByCondition(int RegionID, string StoreID, DateTime? beginReceiveDate, DateTime? endReceiveDate, string status)
    {
        List<Receive> list = new List<Receive>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetReceivesByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginReceiveDate", beginReceiveDate.HasValue ? beginReceiveDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endReceiveDate", endReceiveDate.HasValue ? endReceiveDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@status", string.IsNullOrEmpty(status) ? System.DBNull.Value.ToString() : status);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    bool? isAccept = null;
                    DateTime? auditingDate = null;
                    list.Add(new Receive(
                        reader["ReceiveID"].ToString(),
                        DateTime.Parse(reader["ReceiveDate"].ToString()),
                        reader["IsAccept"].ToString() == System.DBNull.Value.ToString() ? isAccept : bool.Parse(reader["IsAccept"].ToString()),
                        reader["AuditingDate"].ToString() == System.DBNull.Value.ToString() ? auditingDate : DateTime.Parse(reader["AuditingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["AuditingDescription"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString(),
                        reader["OrderID"].ToString(),
                        reader["DeliveryNO"].ToString()

                        ));
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<ReceiveDetail> getReceiveDetailByReceiveID(string ReceiveID)
    {
        List<ReceiveDetail> list = new List<ReceiveDetail>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandText = string.Format("exec usp_GetReceiveDetailByReceiveID '{0}'", ReceiveID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new ReceiveDetail(ReceiveID,
                        reader["MaterialID"].ToString(),
                        reader["MaterialName"].ToString(),
                        reader["OrderUnit"].ToString(),
                        reader["Spec"].ToString(),
                        float.Parse(reader["OrderQty"].ToString()), float.Parse(reader["ActualQty"].ToString()), float.Parse(reader["DifferenceQty"].ToString()))
                        );
                }
                reader.Close();
            }
        }
        return list;
    }
    public static Receive getReceive(string ReceiveID)
    {
        Receive receive = null;
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandText = string.Format("usp_GetReceive '{0}'", ReceiveID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    bool? isAccept = null;
                    DateTime? auditingDate = null;
                    receive = new Receive(
                        reader["ReceiveID"].ToString(),
                        DateTime.Parse(reader["ReceiveDate"].ToString()),
                        reader["IsAccept"].ToString() == System.DBNull.Value.ToString() ? isAccept : bool.Parse(reader["IsAccept"].ToString()),
                        reader["AuditingDate"].ToString() == System.DBNull.Value.ToString() ? auditingDate : DateTime.Parse(reader["AuditingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["AuditingDescription"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString(),
                        reader["OrderID"].ToString(),
                        reader["DeliveryNO"].ToString()
                        );
                }
                reader.Close();
            }
        }
        return receive;
    }
    public static bool AuditingReceive(string StoreID,string ReceiveID, bool isAccept, string AuditingDescription)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_AuditingReceive";
                com.Parameters.AddWithValue("@StoreID", StoreID);
                com.Parameters.AddWithValue("@ReceiveID", ReceiveID);
                com.Parameters.AddWithValue("@IsAccept", isAccept);
                com.Parameters.AddWithValue("@AuditingDescription", AuditingDescription);
                com.Parameters.AddWithValue("@msg_id", DateTime.Now.ToString("yyyyMMdd_HHmmss_ff"));
                com.Connection = con;
                con.Open();
                int r = com.ExecuteNonQuery();
                if (r > 0)
                {
                    return true;
                }
            }
        }
        return false;
    }


    public static List<Return> getReturnsByCondition(int RegionID, string StoreID, DateTime? beginReturnDate, DateTime? endReturnDate, string status)
    {
        List<Return> list = new List<Return>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetReturnsByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginReturnDate", beginReturnDate.HasValue ? beginReturnDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endReturnDate", endReturnDate.HasValue ? endReturnDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@status", string.IsNullOrEmpty(status) ? System.DBNull.Value.ToString() : status);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    bool? isAccept = null;
                    DateTime? auditingDate = null;
                    list.Add(new Return(
                        reader["ReturnID"].ToString(),
                        DateTime.Parse(reader["ReturnDate"].ToString()),
                        reader["IsAccept"].ToString() == System.DBNull.Value.ToString() ? isAccept : bool.Parse(reader["IsAccept"].ToString()),
                        reader["AuditingDate"].ToString() == System.DBNull.Value.ToString() ? auditingDate : DateTime.Parse(reader["AuditingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["AuditingDescription"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString()

                        ));
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<ReturnDetail> getReturnDetailByReturnID(string ReturnID)
    {
        List<ReturnDetail> list = new List<ReturnDetail>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandText = string.Format("exec usp_GetReturnDetailByReturnID '{0}'", ReturnID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new ReturnDetail(ReturnID,
                        reader["MaterialID"].ToString(),
                        reader["MaterialName"].ToString(),
                        reader["OrderUnit"].ToString(),
                        reader["Spec"].ToString(),
                        float.Parse(reader["Qty"].ToString()))
                        );
                }
                reader.Close();
            }
        }
        return list;
    }
    public static Return getReturn(string ReturnID)
    {
        Return rtn = null;
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandText = string.Format("usp_GetReturn '{0}'", ReturnID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    bool? isAccept = null;
                    DateTime? auditingDate = null;
                    rtn = new Return(
                        reader["ReturnID"].ToString(),
                        DateTime.Parse(reader["ReturnDate"].ToString()),
                        reader["IsAccept"].ToString() == System.DBNull.Value.ToString() ? isAccept : bool.Parse(reader["IsAccept"].ToString()),
                        reader["AuditingDate"].ToString() == System.DBNull.Value.ToString() ? auditingDate : DateTime.Parse(reader["AuditingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["AuditingDescription"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString()
                        );
                }
                reader.Close();
            }
        }
        return rtn;
    }
    public static bool AuditingReturn(string StoreID,string ReturnID, bool isAccept, string AuditingDescription)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_AuditingReturn";
                com.Parameters.AddWithValue("@StoreID", StoreID);
                com.Parameters.AddWithValue("@ReturnID", ReturnID);
                com.Parameters.AddWithValue("@IsAccept", isAccept);
                com.Parameters.AddWithValue("@AuditingDescription", AuditingDescription);
                com.Parameters.AddWithValue("@msg_id", DateTime.Now.ToString("yyyyMMdd_HHmmss_ff"));
                com.Connection = con;
                con.Open();
                int r = com.ExecuteNonQuery();
                if (r > 0)
                {
                    return true;
                }
            }
        }
        return false;
    }

    public static List<string> getDistinctStocktakingType()
    {
        List<string> list = new List<string>();
        list.Clear();
        list.Add("全部");
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("exec usp_GetDistinctStocktakingType", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<Stocktaking> getStocktakingByCondition(int RegionID, string StoreID, DateTime? beginStocktakingDate, DateTime? endStocktakingDate, string type)
    {
        List<Stocktaking> list = new List<Stocktaking>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetStocktakingsByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginStocktakingDate", beginStocktakingDate.HasValue ? beginStocktakingDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endStocktakingDate", endStocktakingDate.HasValue ? endStocktakingDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@StocktakingType", string.IsNullOrEmpty(type) ? System.DBNull.Value.ToString() : type);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new Stocktaking(
                        reader["StocktakingID"].ToString(),
                        reader["StocktakingType"].ToString(),
                        DateTime.Parse(reader["StocktakingDate"].ToString()),
                        reader["Description"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString()

                        ));
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<StocktakingDetail> getStocktakingDetailByStocktakingID(string StocktakingID)
    {
        List<StocktakingDetail> list = new List<StocktakingDetail>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandText = string.Format("exec usp_GetStocktakingDetailByStocktakingID '{0}'", StocktakingID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new StocktakingDetail(StocktakingID,
                        reader["MaterialID"].ToString(),
                        reader["MaterialName"].ToString(),
                        reader["OrderUnit"].ToString(),
                        reader["Spec"].ToString(),
                        float.Parse(reader["qty"].ToString())));
                }
                reader.Close();
            }
        }
        return list;
    }

    public static List<string> getDistinctUsingType()
    {
        List<string> list = new List<string>();
        list.Clear();
        list.Add("全部");
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("exec usp_GetDistinctUsingType", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<MaterialUsing> getMaterialUsingsByCondition(int RegionID, string StoreID, DateTime? beginUsingDate, DateTime? endUsingDate, string type)
    {
        List<MaterialUsing> list = new List<MaterialUsing>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_getMaterialUsingsByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginUsingDate", beginUsingDate.HasValue ? beginUsingDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endUsingDate", endUsingDate.HasValue ? endUsingDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@UsingType", string.IsNullOrEmpty(type) ? System.DBNull.Value.ToString() : type);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new MaterialUsing(
                        DateTime.Parse(reader["ClosingStocktakingDate"].ToString()),
                         DateTime.Parse(reader["OpeningStocktakingDate"].ToString()),
                         reader["MaterialID"].ToString(),
                         reader["MaterialName"].ToString(),
                         reader["OrderUnit"].ToString(),
                         reader["Spec"].ToString(),
                         float.Parse(reader["Qty"].ToString()),
                         reader["UsingType"].ToString(),
                        reader["StoreID"].ToString(),
                        reader["CName"].ToString()

                        ));
                }
                reader.Close();
            }
        }
        return list;
    }

    public static List<SalesLog> getSalesLogsByCondition(int RegionID, string StoreID, DateTime? beginDate, DateTime? endDate)
    {
        List<SalesLog> list = new List<SalesLog>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetSalesLogsByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginDate", beginDate.HasValue ? beginDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endDate", endDate.HasValue ? endDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    SalesLog log = new SalesLog();
                    log.StoreID = reader["StoreID"].ToString();
                    log.StoreName = reader["CName"].ToString();
                    log.OperationDate = DateTime.Parse(reader["OperationDate"].ToString()).ToString("yyyy-MM-dd");
                    log.TotalSales = decimal.Parse(reader["TotalSales"].ToString());
                    log.TotalTC = int.Parse(reader["TotalTC"].ToString());
                    log.DinnerSales = decimal.Parse(reader["DinnerSales"].ToString());
                    log.DinnerTC = int.Parse(reader["DinnerTC"].ToString());
                    log.TakeAwaySales = decimal.Parse(reader["TakeAwaySales"].ToString());
                    log.TakeAwayTC = int.Parse(reader["TakeAwayTC"].ToString());
                    log.DeliverySales = decimal.Parse(reader["DeliverySales"].ToString());
                    log.DeliveryTC = int.Parse(reader["DeliveryTC"].ToString());
                    log.LaborHour = float.Parse(reader["LaborHour"].ToString());
                    log.Electricity = int.Parse(reader["Electricity"].ToString());
                    log.Water = int.Parse(reader["Water"].ToString());
                    log.Gas = int.Parse(reader["Gas"].ToString());
                    log.P1 = int.Parse(reader["Product1"].ToString());
                    log.P2 = int.Parse(reader["Product2"].ToString());
                    log.P3 = int.Parse(reader["Product3"].ToString());
                    log.P4 = int.Parse(reader["Product4"].ToString());
                    log.P5 = int.Parse(reader["Product5"].ToString());
                    log.P6 = int.Parse(reader["Product6"].ToString());
                    log.P7 = int.Parse(reader["Product7"].ToString());
                    log.P8 = int.Parse(reader["Product8"].ToString());
                    log.P9 = int.Parse(reader["Product9"].ToString());
                    log.P10 = int.Parse(reader["Product10"].ToString());
                    log.OperationReport = reader["OperationReport"].ToString();
                    log.MarketingReport = reader["MarketingReport"].ToString();
                    log.RepairReport = reader["RepairReport"].ToString();
                    log.EventReport = reader["EventReport"].ToString();
                    list.Add(log);

                }
                reader.Close();
            }
        }
        return list;
    }

    public static List<Forecast> getForecastByCondition(int RegionID, string StoreID, DateTime? beginDate, DateTime? endDate)
    {
        List<Forecast> list = new List<Forecast>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetForecastByCondition";
                com.Parameters.AddWithValue("@RegionID", RegionID);
                com.Parameters.AddWithValue("@StoreID", string.IsNullOrEmpty(StoreID) ? System.DBNull.Value.ToString() : StoreID);
                com.Parameters.AddWithValue("@beginDate", beginDate.HasValue ? beginDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Parameters.AddWithValue("@endDate", endDate.HasValue ? endDate.Value.ToString("yyyy-MM-dd") : System.DBNull.Value.ToString());
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Forecast log = new Forecast();
                    log.StoreID = reader["StoreID"].ToString();
                    log.StoreName = reader["CName"].ToString();
                    log.OperationDate = DateTime.Parse(reader["OperationDate"].ToString()).ToString("yyyy-MM-dd");
                    log.BudgetSales = decimal.Parse(reader["BudgetSales"].ToString());
                    log.BudgetTC = int.Parse(reader["BudgetTC"].ToString());
                    log.TargetSales = decimal.Parse(reader["TargetSales"].ToString());
                    log.TargetTC = int.Parse(reader["TargetTC"].ToString());
                    log.TargetDinnerSales = decimal.Parse(reader["TargetDinnerSales"].ToString());
                    log.TargetDinnerTC = int.Parse(reader["TargetDinnerTC"].ToString());
                    log.TargetTakeAwaySales = decimal.Parse(reader["TargetTakeAwaySales"].ToString());
                    log.TargetTakeAwayTC = int.Parse(reader["TargetTakeAwayTC"].ToString());
                    log.TargetDeliverySales = decimal.Parse(reader["TargetDeliverySales"].ToString());
                    log.TargetDeliveryTC = int.Parse(reader["TargetDeliveryTC"].ToString());
                    log.TargetLaborHour = float.Parse(reader["TargetLaborHour"].ToString());
                    log.TargetElectricity = int.Parse(reader["TargetElectricity"].ToString());
                    log.TargetWater = int.Parse(reader["TargetWater"].ToString());
                    log.TargetGas = int.Parse(reader["TargetGas"].ToString());
                    list.Add(log);

                }
                reader.Close();
            }
        }
        return list;
    }


    public static List<string> getDistinctMaterialStatus()
    {
        List<string> list = new List<string>();
        list.Add("全部");
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("exec usp_getDistinctMaterialStatus", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    if (reader[0] == System.DBNull.Value)
                    {
                        list.Add("未知");
                    }
                    else
                    {
                        list.Add(reader[0].ToString());
                    }
                    
                }
                reader.Close();
            }
        }
        return list;
    }

    public static List<Material> getMaterialsByCondition(string materialID, string materialName, string status,string brand,string region)
    {
        List<Material> list = new List<Material>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetMaterialsByCondition";
                com.Parameters.AddWithValue("@MaterialID", string.IsNullOrEmpty(materialID) ? System.DBNull.Value.ToString() : materialID);
                com.Parameters.AddWithValue("@MaterialName", string.IsNullOrEmpty(materialName) ? System.DBNull.Value.ToString() : materialName);
                com.Parameters.AddWithValue("@status", status=="未知" ? System.DBNull.Value.ToString() : status);
                com.Parameters.AddWithValue("@Brand", brand == "未知" ? System.DBNull.Value.ToString() : brand);
                com.Parameters.AddWithValue("@Region", region == "未知" ? System.DBNull.Value.ToString() : region);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Material m = new Material();
                    m.SystemID = int.Parse(reader["SystemID"].ToString());
                    m.MaterialID = reader["MaterialID"].ToString();
                    m.MaterialName = reader["MaterialName"].ToString();
                    m.Model = reader["Model"].ToString();
                    m.MaxOrderValue = float.Parse(reader["MaxOrderValue"].ToString());
                    m.MinOrderValue = float.Parse(reader["MinOrderValue"].ToString());
                    m.Order_Cycle = reader["Order_Cycle"].ToString();
                    m.Stocktaking_Cycle = reader["Stocktaking_Cycle"].ToString();
                    m.StockUnit = reader["StockUnit"].ToString();
                    m.StorageType = reader["StorageType"].ToString();
                    m.Status = reader["Status"]==System.DBNull.Value?"未知":reader["Status"].ToString();
                    m.Spec = reader["Spec"].ToString();
                    m.Source = reader["Source"].ToString();
                    m.Property = reader["Property"].ToString();
                    m.LogisticCategoryFullName = reader["LogisticCategoryFullName"].ToString();
                    m.Description = reader["Description"].ToString();
                    m.BaseUnitConvertOrderUnitValue = float.Parse(reader["BUnitConvertOUnitValue"].ToString());
                    m.BaseUnitConvertStockUnitValue = float.Parse(reader["BUnitConvertSUnitValue"].ToString());
                    m.BaseUnit = reader["BaseUnit"].ToString();
                    m.StockUnit = reader["StockUnit"].ToString();
                    m.OrderUnit = reader["OrderUnit"].ToString();
                    m.DeliveryType = reader["DeliveryType"].ToString();
                    m.ApplyBrand = reader["ApplyBrand"].ToString().Split('|');
                    m.ApplyRegion = reader["ApplyRegion"].ToString().Split('|');
                    m.LogisticStatus = reader["ReferenceText"].ToString();
                    list.Add(m);
                }
                reader.Close();
            }
        }
        return list;
    }


    public static List<string> getDistinctMaterialOrderCycle()
    {
        List<string> list = new List<string>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("exec usp_GetDistinctOrderCycle", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                        list.Add(reader[0].ToString());
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<string> getDistinctMaterialStocktakingCycle()
    {
        List<string> list = new List<string>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("exec usp_GetDistinctStocktakingCycle", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
                reader.Close();
            }
        }
        return list;
    }
    public static List<string> getDistinctMaterialDeliveryType()
    {
        List<string> list = new List<string>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("exec usp_GetDistinctDeliveryType", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader[0].ToString());
                }
                reader.Close();
            }
        }
        return list;
    }

    public static Material getMaterialsByMaterialID(string materialID)
    {
        Material m = null;
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {

                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_GetMaterialByMaterialID";
                com.Parameters.AddWithValue("@MaterialID", materialID);
                com.Connection = con;
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    m = new Material();
                    m.SystemID = int.Parse(reader["SystemID"].ToString());
                    m.MaterialID = reader["MaterialID"].ToString();
                    m.MaterialName = reader["MaterialName"].ToString();
                    m.Model = reader["Model"].ToString();
                    m.MaxOrderValue = float.Parse(reader["MaxOrderValue"].ToString());
                    m.MinOrderValue = float.Parse(reader["MinOrderValue"].ToString());
                    m.Order_Cycle = reader["Order_Cycle"].ToString();
                    m.Stocktaking_Cycle = reader["Stocktaking_Cycle"].ToString();
                    m.StockUnit = reader["StockUnit"].ToString();
                    m.StorageType = reader["StorageType"].ToString();
                    m.Status = reader["Status"].ToString();
                    m.Spec = reader["Spec"].ToString();
                    m.Source = reader["Source"].ToString();
                    m.Property = reader["Property"].ToString();
                    m.LogisticCategoryFullName = reader["LogisticCategoryFullName"].ToString();
                    m.Description = reader["Description"].ToString();
                    m.BaseUnitConvertOrderUnitValue = float.Parse(reader["BUnitConvertOUnitValue"].ToString());
                    m.BaseUnitConvertStockUnitValue = float.Parse(reader["BUnitConvertSUnitValue"].ToString());
                    m.BaseUnit = reader["BaseUnit"].ToString();
                    m.StockUnit = reader["StockUnit"].ToString();
                    m.OrderUnit = reader["OrderUnit"].ToString();
                    m.DeliveryType = reader["DeliveryType"].ToString();
                    m.ApplyBrand = reader["ApplyBrand"].ToString().Split('|');
                    m.ApplyRegion = reader["ApplyRegion"].ToString().Split('|');
                }
                reader.Close();
            }
        }
        return m;
    }

    public static bool UpdateMaterialBaseInfo(string materialID, float MinOrderValue, float MaxOrderValue, string OrderCycle, string StocktakingCycle, string DeliveryType, string ApplyBrand, string ApplyRegion,string Description)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.Connection = con;
                con.Open();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "usp_UpdateMaterial";
                com.Parameters.AddWithValue("@MaterialID", string.IsNullOrEmpty(materialID) ? System.DBNull.Value.ToString() : materialID);
                com.Parameters.AddWithValue("@MinOrderValue", MinOrderValue);
                com.Parameters.AddWithValue("@MaxOrderValue", MaxOrderValue);
                com.Parameters.AddWithValue("@OrderCycle", string.IsNullOrEmpty(OrderCycle)?System.DBNull.Value.ToString():OrderCycle);
                com.Parameters.AddWithValue("@StocktakingCycle", string.IsNullOrEmpty(StocktakingCycle) ? System.DBNull.Value.ToString() : StocktakingCycle);
                com.Parameters.AddWithValue("@DeliveryType", string.IsNullOrEmpty(DeliveryType) ? System.DBNull.Value.ToString() : DeliveryType);
                com.Parameters.AddWithValue("@ApplyBrand", string.IsNullOrEmpty(ApplyBrand) ? System.DBNull.Value.ToString() : ApplyBrand);
                com.Parameters.AddWithValue("@ApplyRegion", string.IsNullOrEmpty(ApplyRegion) ? System.DBNull.Value.ToString() : ApplyRegion);
                com.Parameters.AddWithValue("@Description", string.IsNullOrEmpty(Description) ? System.DBNull.Value.ToString() : Description);

                int i = com.ExecuteNonQuery();
                if (i > 0)
                {
                    return true;
                }
            }
        }
        return false;
    }

    public static List<Material> GetMaterialSynchronizationForLogistic()
    {
        List<Material> list = new List<Material>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("EXEC dbo.usp_GetMaterialSynchronizationForLogistic", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Material m = new Material();
                    m.MaterialID = reader["MaterialID"].ToString();
                    m.MaterialName = reader["MaterialName"].ToString();
                    m.Spec = reader["Spec"].ToString();
                    m.OrderUnit = reader["OrderUnit"].ToString();
                    m.LogisticStatus = reader["Status"].ToString();
                    m.LogisticCategoryFullName = reader["LogisticCategoryFullName"].ToString();
                    list.Add(m);
                }
                reader.Close();
            }
        }
        return list;
    }

    public static List<Material> GetMaterialSynchronizationForSMS()
    {
        List<Material> list = new List<Material>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("EXEC dbo.usp_GetMaterialSynchronizationForSMS", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Material m = new Material();
                    m.MaterialID = reader["MaterialID"].ToString();
                    m.MaterialName = reader["MaterialName"].ToString();
                    m.Spec = reader["Spec"].ToString();
                    m.OrderUnit = reader["OrderUnit"].ToString();
                    m.LogisticStatus = reader["Status"].ToString();
                    m.LogisticCategoryFullName = reader["LogisticCategoryFullName"].ToString();
                    list.Add(m);
                }
                reader.Close();
            }
        }
        return list;
    }

    public static List<MatchMaterial> GetMaterialSynchronizationForKey()
    {
        List<MatchMaterial> list = new List<MatchMaterial>();
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand("EXEC dbo.usp_GetMaterialSynchronizationForKey", con))
            {
                con.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    MatchMaterial mm = new MatchMaterial();
                    mm.MaterialID = reader["MaterialID"].ToString();
                    mm.MaterialName = reader["MaterialName"].ToString();
                    mm.Logistic_MaterialName = reader["Logistic_MaterialName"].ToString();
                    mm.BaseUnit = reader["BaseUnit"].ToString();
                    mm.Logistic_BaseUnit = reader["Logistic_BaseUnit"].ToString();
                    mm.OrderUnit = reader["OrderUnit"].ToString();
                    mm.Logistic_OrderUnit = reader["Logistic_OrderUnit"].ToString();
                    mm.StockUnit = reader["StockUnit"].ToString();
                    mm.Logistic_StockUnit = reader["Logistic_StockUnit"].ToString();
                    mm.BUnitConvertOUnitValue = float.Parse(reader["BUnitConvertOUnitValue"].ToString());
                    mm.Logistic_BUnitConvertOUnitValue = float.Parse(reader["Logistic_BUnitConvertOUnitValue"].ToString());
                    mm.BUnitConvertSUnitValue = float.Parse(reader["BUnitConvertSUnitValue"].ToString());
                    mm.Logistic_BUnitConvertSUnitValue = float.Parse(reader["Logistic_BUnitConvertSUnitValue"].ToString());
                    mm.LogisticStatus = reader["LogisticStatus"].ToString();
                    mm.Logistic_LogisticStatus = reader["Logistic_LogisticStatus"].ToString();
                    list.Add(mm);
                }
                reader.Close();
            }
        }
        return list;
    }

    public static void InsertMaterials(string MaterialIDs)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand(string.Format("EXEC usp_InsertMaterial '({0})'", MaterialIDs), con))
            {
                con.Open();
                com.ExecuteNonQuery();
            }
        }
    }

    public static void ChangedMaterialsStatus(string MaterialIDs)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand(string.Format("EXEC usp_ChangeMaterialStatus '({0})'", MaterialIDs), con))
            {
                con.Open();
                com.ExecuteNonQuery();
            }
        }
    }

    public static void ChangedMaterialStatus(string MaterialID,string status)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            using (SqlCommand com = new SqlCommand(string.Format("EXEC usp_ChangeSingleMaterialStatus '{0}','{1}'", MaterialID,status), con))
            {
                con.Open();
                com.ExecuteNonQuery();
            }
        }
    }
}
